// Package sqldump provides pure Go implementation for SQLite database dump operations
package sqldump

import (
	"database/sql"
	"fmt"
	"io"
	"regexp"
	"strings"
	"time"
)

// DumpOptions contains options for SQL dump operations
type DumpOptions struct {
	IncludeSchema       bool // Include CREATE TABLE statements
	IncludeData         bool // Include INSERT statements
	IncludeIndexes      bool // Include CREATE INDEX statements
	IncludeTriggers     bool // Include CREATE TRIGGER statements
	ExcludeTables       []string // Tables to exclude from dump
	OnlyTables          []string // Only dump these tables (if specified)
	BatchSize           int  // Number of rows per INSERT statement batch
	CompressOutput      bool // Whether to compress whitespace in output
	DisableForeignKeys  bool // Add PRAGMA foreign_keys=OFF at beginning
}

// DefaultDumpOptions returns default options for SQL dump
func DefaultDumpOptions() *DumpOptions {
	return &DumpOptions{
		IncludeSchema:      true,
		IncludeData:        true,
		IncludeIndexes:     true,
		IncludeTriggers:    true,
		BatchSize:          100,
		CompressOutput:     false,
		DisableForeignKeys: true,
	}
}

// SQLDumper provides methods to dump SQLite database to SQL statements
type SQLDumper struct {
	db      *sql.DB
	options *DumpOptions
}

// NewSQLDumper creates a new SQL dumper instance
func NewSQLDumper(db *sql.DB, options *DumpOptions) *SQLDumper {
	if options == nil {
		options = DefaultDumpOptions()
	}
	return &SQLDumper{
		db:      db,
		options: options,
	}
}

// DumpToWriter writes the complete SQL dump to the provided writer
func (d *SQLDumper) DumpToWriter(w io.Writer) error {
	// Write SQL dump header
	if err := d.writeHeader(w); err != nil {
		return fmt.Errorf("failed to write header: %w", err)
	}

	// Get list of tables to dump
	tables, err := d.getTables()
	if err != nil {
		return fmt.Errorf("failed to get tables: %w", err)
	}

	// Filter tables based on options
	tables = d.filterTables(tables)

	// Dump schema if requested
	if d.options.IncludeSchema {
		if err := d.dumpSchema(w, tables); err != nil {
			return fmt.Errorf("failed to dump schema: %w", err)
		}
	}

	// Dump data if requested
	if d.options.IncludeData {
		if err := d.dumpData(w, tables); err != nil {
			return fmt.Errorf("failed to dump data: %w", err)
		}
	}

	// Dump indexes if requested
	if d.options.IncludeIndexes {
		if err := d.dumpIndexes(w, tables); err != nil {
			return fmt.Errorf("failed to dump indexes: %w", err)
		}
	}

	// Dump triggers if requested
	if d.options.IncludeTriggers {
		if err := d.dumpTriggers(w, tables); err != nil {
			return fmt.Errorf("failed to dump triggers: %w", err)
		}
	}

	// Write SQL dump footer
	if err := d.writeFooter(w); err != nil {
		return fmt.Errorf("failed to write footer: %w", err)
	}

	return nil
}

// writeHeader writes the SQL dump header with metadata and pragmas
func (d *SQLDumper) writeHeader(w io.Writer) error {
	header := fmt.Sprintf(`-- ============================================================
-- SQL Dump generated by HomeBox Enhanced
-- Generated on: %s
-- Database: SQLite
-- Dump version: 1.0
-- ============================================================

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;

`, time.Now().Format("2006-01-02 15:04:05"))

	_, err := w.Write([]byte(header))
	return err
}

// writeFooter writes the SQL dump footer
func (d *SQLDumper) writeFooter(w io.Writer) error {
	footer := `
COMMIT;
PRAGMA foreign_keys=ON;

-- ============================================================
-- End of SQL Dump
-- ============================================================
`
	_, err := w.Write([]byte(footer))
	return err
}

// getTables retrieves all table names from the database
func (d *SQLDumper) getTables() ([]string, error) {
	query := `SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name`
	
	rows, err := d.db.Query(query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var tables []string
	for rows.Next() {
		var tableName string
		if err := rows.Scan(&tableName); err != nil {
			return nil, err
		}
		tables = append(tables, tableName)
	}

	return tables, rows.Err()
}

// filterTables filters the table list based on options
func (d *SQLDumper) filterTables(tables []string) []string {
	var filtered []string

	for _, table := range tables {
		// Skip if in exclude list
		if d.isExcluded(table) {
			continue
		}

		// If only-tables is specified, only include those
		if len(d.options.OnlyTables) > 0 && !d.isIncluded(table) {
			continue
		}

		filtered = append(filtered, table)
	}

	return filtered
}

// isExcluded checks if table is in exclude list
func (d *SQLDumper) isExcluded(table string) bool {
	for _, excluded := range d.options.ExcludeTables {
		if table == excluded {
			return true
		}
	}
	return false
}

// isIncluded checks if table is in only-tables list
func (d *SQLDumper) isIncluded(table string) bool {
	for _, included := range d.options.OnlyTables {
		if table == included {
			return true
		}
	}
	return false
}

// dumpSchema dumps CREATE TABLE statements for all tables
func (d *SQLDumper) dumpSchema(w io.Writer, tables []string) error {
	if _, err := w.Write([]byte("\n-- ============================================================\n-- Table structure\n-- ============================================================\n\n")); err != nil {
		return err
	}

	for _, table := range tables {
		// Get CREATE TABLE statement
		query := `SELECT sql FROM sqlite_master WHERE type='table' AND name=?`
		
		var createSQL string
		if err := d.db.QueryRow(query, table).Scan(&createSQL); err != nil {
			return fmt.Errorf("failed to get CREATE statement for table %s: %w", table, err)
		}

		// Write DROP TABLE statement
		dropSQL := fmt.Sprintf("DROP TABLE IF EXISTS `%s`;\n", table)
		if _, err := w.Write([]byte(dropSQL)); err != nil {
			return err
		}

		// Write CREATE TABLE statement
		createSQL = strings.TrimSpace(createSQL)
		if !strings.HasSuffix(createSQL, ";") {
			createSQL += ";"
		}
		createSQL += "\n\n"
		
		if _, err := w.Write([]byte(createSQL)); err != nil {
			return err
		}
	}

	return nil
}

// dumpData dumps INSERT statements for all table data
func (d *SQLDumper) dumpData(w io.Writer, tables []string) error {
	if _, err := w.Write([]byte("\n-- ============================================================\n-- Table data\n-- ============================================================\n\n")); err != nil {
		return err
	}

	for _, table := range tables {
		if err := d.dumpTableData(w, table); err != nil {
			return fmt.Errorf("failed to dump data for table %s: %w", table, err)
		}
	}

	return nil
}

// dumpTableData dumps data for a specific table
func (d *SQLDumper) dumpTableData(w io.Writer, table string) error {
	// Get column information
	columns, err := d.getTableColumns(table)
	if err != nil {
		return fmt.Errorf("failed to get columns for table %s: %w", table, err)
	}

	if len(columns) == 0 {
		return nil // Skip empty tables
	}

	// Count total rows
	var rowCount int
	if err := d.db.QueryRow(fmt.Sprintf("SELECT COUNT(*) FROM `%s`", table)).Scan(&rowCount); err != nil {
		return fmt.Errorf("failed to count rows in table %s: %w", table, err)
	}

	if rowCount == 0 {
		return nil // Skip empty tables
	}

	// Write table data header
	if _, err := w.Write([]byte(fmt.Sprintf("-- Data for table `%s` (%d rows)\n", table, rowCount))); err != nil {
		return err
	}

	// Create column list for INSERT statement
	columnList := "`" + strings.Join(columns, "`, `") + "`"
	
	// Query data in batches
	batchSize := d.options.BatchSize
	if batchSize <= 0 {
		batchSize = 100
	}

	for offset := 0; offset < rowCount; offset += batchSize {
		query := fmt.Sprintf("SELECT %s FROM `%s` LIMIT %d OFFSET %d", columnList, table, batchSize, offset)
		
		rows, err := d.db.Query(query)
		if err != nil {
			return fmt.Errorf("failed to query data from table %s: %w", table, err)
		}

		// Process batch
		insertSQL, err := d.buildInsertStatements(table, columns, rows)
		rows.Close()
		
		if err != nil {
			return fmt.Errorf("failed to build insert statements for table %s: %w", table, err)
		}

		if insertSQL != "" {
			if _, err := w.Write([]byte(insertSQL)); err != nil {
				return err
			}
		}
	}

	if _, err := w.Write([]byte("\n")); err != nil {
		return err
	}

	return nil
}

// getTableColumns retrieves column names for a specific table
func (d *SQLDumper) getTableColumns(table string) ([]string, error) {
	query := fmt.Sprintf("PRAGMA table_info(`%s`)", table)
	
	rows, err := d.db.Query(query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var columns []string
	for rows.Next() {
		var cid int
		var name, dataType string
		var notNull, pk int
		var defaultValue interface{}
		
		if err := rows.Scan(&cid, &name, &dataType, &notNull, &defaultValue, &pk); err != nil {
			return nil, err
		}
		
		columns = append(columns, name)
	}

	return columns, rows.Err()
}

// buildInsertStatements builds INSERT statements from query results
func (d *SQLDumper) buildInsertStatements(table string, columns []string, rows *sql.Rows) (string, error) {
	var result strings.Builder
	
	// Prepare values slice
	values := make([]interface{}, len(columns))
	valuePtrs := make([]interface{}, len(columns))
	for i := range values {
		valuePtrs[i] = &values[i]
	}

	var insertValues []string
	rowCount := 0

	for rows.Next() {
		if err := rows.Scan(valuePtrs...); err != nil {
			return "", err
		}

		// Convert values to SQL format
		var formattedValues []string
		for _, val := range values {
			formattedValues = append(formattedValues, d.formatValue(val))
		}

		insertValues = append(insertValues, "("+strings.Join(formattedValues, ", ")+")")
		rowCount++

		// Write batch when batch size is reached
		if rowCount >= d.options.BatchSize {
			columnList := "`" + strings.Join(columns, "`, `") + "`"
			result.WriteString(fmt.Sprintf("INSERT INTO `%s` (%s) VALUES \n", table, columnList))
			result.WriteString(strings.Join(insertValues, ",\n"))
			result.WriteString(";\n")
			
			insertValues = nil
			rowCount = 0
		}
	}

	// Write remaining values
	if len(insertValues) > 0 {
		columnList := "`" + strings.Join(columns, "`, `") + "`"
		result.WriteString(fmt.Sprintf("INSERT INTO `%s` (%s) VALUES \n", table, columnList))
		result.WriteString(strings.Join(insertValues, ",\n"))
		result.WriteString(";\n")
	}

	return result.String(), rows.Err()
}

// formatValue formats a value for SQL insertion
func (d *SQLDumper) formatValue(val interface{}) string {
	if val == nil {
		return "NULL"
	}

	switch v := val.(type) {
	case []byte:
		if len(v) == 0 {
			return "NULL"
		}
		// Check if it's a text string or binary data
		if d.isText(v) {
			return d.escapeString(string(v))
		}
		// For binary data, use hex encoding
		return fmt.Sprintf("X'%X'", v)
	case string:
		return d.escapeString(v)
	case int64, int32, int16, int8, int:
		return fmt.Sprintf("%d", v)
	case uint64, uint32, uint16, uint8, uint:
		return fmt.Sprintf("%d", v)
	case float64, float32:
		return fmt.Sprintf("%g", v)
	case bool:
		if v {
			return "1"
		}
		return "0"
	case time.Time:
		return d.escapeString(v.Format("2006-01-02 15:04:05"))
	default:
		return d.escapeString(fmt.Sprintf("%v", v))
	}
}

// isText checks if byte slice contains text data
func (d *SQLDumper) isText(data []byte) bool {
	if len(data) == 0 {
		return true
	}
	
	// Simple heuristic: if most bytes are printable ASCII, treat as text
	printableCount := 0
	for _, b := range data {
		if (b >= 32 && b <= 126) || b == 9 || b == 10 || b == 13 {
			printableCount++
		}
	}
	
	return float64(printableCount)/float64(len(data)) > 0.7
}

// escapeString escapes a string for SQL insertion
func (d *SQLDumper) escapeString(s string) string {
	// Replace single quotes with two single quotes
	escaped := strings.ReplaceAll(s, "'", "''")
	return "'" + escaped + "'"
}

// dumpIndexes dumps CREATE INDEX statements
func (d *SQLDumper) dumpIndexes(w io.Writer, tables []string) error {
	if _, err := w.Write([]byte("\n-- ============================================================\n-- Indexes\n-- ============================================================\n\n")); err != nil {
		return err
	}

	// Get all indexes except those automatically created for PRIMARY KEY and UNIQUE constraints
	query := `
		SELECT sql 
		FROM sqlite_master 
		WHERE type='index' 
		  AND sql IS NOT NULL
		  AND name NOT LIKE 'sqlite_autoindex_%'
		ORDER BY name
	`
	
	rows, err := d.db.Query(query)
	if err != nil {
		return err
	}
	defer rows.Close()

	for rows.Next() {
		var indexSQL string
		if err := rows.Scan(&indexSQL); err != nil {
			return err
		}

		// Filter indexes for our tables only
		if d.indexBelongsToTables(indexSQL, tables) {
			indexSQL = strings.TrimSpace(indexSQL)
			if !strings.HasSuffix(indexSQL, ";") {
				indexSQL += ";"
			}
			indexSQL += "\n"
			
			if _, err := w.Write([]byte(indexSQL)); err != nil {
				return err
			}
		}
	}

	return rows.Err()
}

// indexBelongsToTables checks if an index belongs to one of our tables
func (d *SQLDumper) indexBelongsToTables(indexSQL string, tables []string) bool {
	for _, table := range tables {
		// Simple pattern matching for table name in index SQL
		if strings.Contains(indexSQL, "`"+table+"`") || strings.Contains(indexSQL, table+" ") {
			return true
		}
	}
	return false
}

// dumpTriggers dumps CREATE TRIGGER statements
func (d *SQLDumper) dumpTriggers(w io.Writer, tables []string) error {
	if _, err := w.Write([]byte("\n-- ============================================================\n-- Triggers\n-- ============================================================\n\n")); err != nil {
		return err
	}

	query := `
		SELECT sql 
		FROM sqlite_master 
		WHERE type='trigger' 
		  AND sql IS NOT NULL
		ORDER BY name
	`
	
	rows, err := d.db.Query(query)
	if err != nil {
		return err
	}
	defer rows.Close()

	for rows.Next() {
		var triggerSQL string
		if err := rows.Scan(&triggerSQL); err != nil {
			return err
		}

		// Filter triggers for our tables only
		if d.triggerBelongsToTables(triggerSQL, tables) {
			triggerSQL = strings.TrimSpace(triggerSQL)
			if !strings.HasSuffix(triggerSQL, ";") {
				triggerSQL += ";"
			}
			triggerSQL += "\n"
			
			if _, err := w.Write([]byte(triggerSQL)); err != nil {
				return err
			}
		}
	}

	return rows.Err()
}

// triggerBelongsToTables checks if a trigger belongs to one of our tables
func (d *SQLDumper) triggerBelongsToTables(triggerSQL string, tables []string) bool {
	for _, table := range tables {
		// Look for ON table_name pattern in trigger SQL
		pattern := fmt.Sprintf("(?i)ON\\s+`?%s`?", regexp.QuoteMeta(table))
		matched, _ := regexp.MatchString(pattern, triggerSQL)
		if matched {
			return true
		}
	}
	return false
}